﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class DSTREQYARNData : BaseFunction
    {
        
        public DSTREQYARNData()
            : base()
        {

        }
        public DSTREQYARNData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetVKPOS(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "VKPOS";
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();

            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select VKPAUFKRS,VKPAUFLFD,VKPAUFPOS,TO_NUMBER(VKPKUNDNR) AS CUSTOMER, "
                    + " TRIM(A.VKPART)||'-'||TRIM(A.VKPFARBE) as ARTICLE,VKPKDARTNR,VKPMENGE1*0.01 AS QTY,VKPMEEH "
                    + " from vkpos a  where a.vkpsa<>'AJ'  "
                    + " AND TRIM(A.VKPAUFKRS)||'-'||TO_NUMBER(A.VKPAUFLFD)= '" + ARTICLE + "' " 
                    + " ORDER BY 1,2,3 "
                    ;
                
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["VKPKDARTNR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["VKPKDARTNR"] = value;
                    }
                }                
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetWARPYARN(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WARPYARN";
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();

            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = " select trim(C.WAFART)||'-'||TRIM(C.WAFZUSATZ) AS ARTICLE, "
                    + " TRIM(A.BAGGART) AS BAGGART,TRIM(A.BAGGFARBE) AS BAGGFARBE,TRIM(A.BAGBEM) AS BAGBEM,  "
                    + " C.WAFGRPBEZ,Round(A.BAGKGBRUT * WAFGRPBEZ * DECODE(NVL(TRIM(GAGKGBRUT),'-'),'-',1,GAGKGBRUT*0.0000001) * 0.01 * 0.01*0.01, 1) AS REQ, "
                    + " (CASE WHEN SUBSTR(A.BAGGART,INSTR(A.BAGGART,'-')+1,1)>'9' THEN "
                    + " SUBSTR(TRIM(A.BAGGART),INSTR(A.BAGGART,'-')+2)  "
                    + " ELSE SUBSTR(TRIM(A.BAGGART),INSTR(A.BAGGART,'-')+1) END ) AS COUNTS, "
                    + " replace(replace(TRIM(E.BAGFILLER),chr(13),','),chr(10),'') AS REMARK,to_number(NVL(TRIM(E.BAGKGNET),0))*0.001 AS REQ1 "
                    + " from bastga A,WASTKE B,WAFARBEN C,GASTGA D,BASTGAD E "
                    + " where A.BAGMANDANT = B.WAKMANDANT AND A.BAGART = B.WAKKART  "
                    + " AND A.BAGVAR = B.WAKKVAR AND A.BAGFARBE = B.WAKKFARBE "
                    + " AND B.WAKMANDANT = C.WAFMANDANT AND B.WAKART = C.WAFART "
                    + " AND B.WAKWCOL = C.WAFZUSATZ AND C.WAFZUART = 'C' "
                    + " AND A.BAGMANDANT = D.GAGMANDANT(+) AND A.BAGGART = D.GAGART(+) "
                    + " AND A.BAGGVAR = D.GAGVAR(+)  "
                    + " AND A.BAGMANDANT = E.BAGMANDANT(+) AND A.BAGART = E.BAGART(+) "
                    + " AND A.BAGVAR = E.BAGVAR(+) AND A.BAGFARBE = E.BAGFARBE(+) "
                    + " AND A.BAGPOS = E.BAGPOS(+) "
                    + " AND C.WAFMANDANT ='00' "
                    + " AND C.WAFKUNDNR = '00000000' AND C.WAFWERK= '   ' AND C.WAFABT = '   ' "
                    + " AND trim(C.WAFART)||'-'||TRIM(C.WAFZUSATZ) = '" + ARTICLE + "' "
                    + " ORDER BY 1,2,3 "
                    ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["BAGBEM"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["BAGBEM"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["REMARK"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["REMARK"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetWEFTYARN(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WEFTYARN";
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();

            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT trim(B.WAFART)||'-'||trim(B.WAFZUSATZ) as ARTICLE, "
                    + " trim(A.WAGGART) as WAGGART,A.WAGGFARBE,TRIM(A.WAGBEM) AS WAGBEM, "
                    + " WAFMUDESS,Round( WAGKGBRUT * WAFMUDESS*DECODE(NVL(TRIM(GAGKGBRUT),'-'),'-',1,GAGKGBRUT*0.0000001)*0.01*0.001,1) AS REQ, "
                    + " (CASE WHEN SUBSTR(A.WAGGART,INSTR(A.WAGGART,'-')+1,1)>'9' THEN "
                    + " SUBSTR(TRIM(A.WAGGART),INSTR(A.WAGGART,'-')+2) "
                    + " ELSE SUBSTR(TRIM(A.WAGGART),INSTR(A.WAGGART,'-')+1) END ) AS COUNTS "
                    + " FROM WASTGA A,WAFARBEN B,GASTGA C "
                    + " WHERE A.WAGMANDANT = B.WAFMANDANT AND A.WAGART = B.WAFART "
                    + " AND A.WAGVAR = B.WAFVAR AND A.WAGWCOL = B.WAFZUSATZ "
                    + " AND A.WAGMANDANT = C.GAGMANDANT(+) AND A.WAGGART = C.GAGART(+) "
                    + " AND A.WAGGVAR = C.GAGVAR(+) AND B.WAFMANDANT ='00' "
                    + " AND B.WAFKUNDNR = '00000000' AND B.WAFWERK= '   ' AND B.WAFABT = '   ' "
                    + " AND B.WAFZUART = 'C' AND trim(B.WAFART)||'-'||trim(B.WAFZUSATZ)='" + ARTICLE + "' "
                    + " ORDER BY 1,2,3 "
                    ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["WAGBEM"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["WAGBEM"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet BuildDSTREQYARNData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetVKPOS(dwUI);
                setReturn.Merge(setResult);
                //setResult = GetWARPYARN(dwUI);
                //setReturn.Merge(setResult);
                //setResult = GetWEFTYARN(dwUI);
                //setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        
    }
}